#include "sqlite.h"

Sqlite::Sqlite(QWidget *parent) : QWidget(parent)
{

}

bool Sqlite::createConnection()     //创建数据库一个连接
{
    //以后就可以同sqlite1和数据库进行连接了
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE","sqlite1");
    db.setDatabaseName("cuibo.db");
    if(!db.open())
    {
        qDebug() << "无法建立数据库连接";
        return false;
    }
    qDebug()<<"数据库连接成功";
    return true;
}

bool Sqlite::createTable()         //创建数据库表
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1");//建立数据库连接
    QSqlQuery query(db);
    QString create_table = "create table goods (id int primary key,name varchar,number int)";
    query.prepare(create_table);
    if(!query.exec())
    {
        qDebug()<<"数据库表创建失败!";
        return false;
    }
    else
    {
        qDebug()<<"数据库表创建成功！";
        return true;
    }
}

bool Sqlite::insertData(int id , QString name ,int number)          //插入信息
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1");
    QSqlQuery query(db);
    QString insertSql = "insert into goods values(?,?,?)";
    query.prepare(insertSql);
    query.addBindValue(id);
    query.addBindValue(name);
    query.addBindValue(number);
    if(!query.exec())
    {
        qDebug()<<"插入失败";
        return false;
    }
    else
    {
        qDebug()<<"插入成功";
        return true;
    }
}

bool Sqlite::deleteData(int id)            //删除一条信息
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1");
    QSqlQuery query(db);
    QString deleteSql = "delete from goods where id = ?";
    query.prepare(deleteSql);
    query.addBindValue(id);
    if(!query.exec())
    {
        qDebug()<<"删除失败";
        return false;
    }
    else
    {
        qDebug()<<"删除成功";
        return true;
    }
}

bool Sqlite::deleteAll()            //清空这个表
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1");
    QSqlQuery query(db);
    QString deleteSql = "delete from goods ";
    query.prepare(deleteSql);
    if(!query.exec())
    {
        qDebug()<<"删除所有信息失败";
        return false;
    }
    else
    {
        qDebug()<<"删除所有信息成功";
        return true;
    }
}

bool Sqlite::queryData(int id)     //查询某条信息
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1");
    QSqlQuery query(db);
    QString selectSql = "select * from goods where id = ?";
    query.prepare(selectSql);
    query.addBindValue(id);
    if(!query.exec())
    {
        qDebug()<<"查询失败";
        return false;
    }
    else
    {
        while(query.next())
        {
            int id2 = query.value(0).toInt();
            QString name = query.value(1).toString();
            int number = query.value(2).toInt();
            qDebug()<<QString("id:%1    name:%2     number:%3").arg(id2).arg(name).arg(number);
        }
        return true;
    }

}

bool Sqlite::queryAll()            //查询所有信息
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1");
    QSqlQuery query(db);
    QString selectSql = "select * from goods";
    query.prepare(selectSql);
    if(!query.exec())
    {
        qDebug()<<"查询失败";
        return false;
    }
    else
    {
        while(query.next())
        {
            int id2 = query.value(0).toInt();
            QString name = query.value(1).toString();
            int number = query.value(2).toInt();
            qDebug()<<QString("id:%1    name:%2     number:%3").arg(id2).arg(name).arg(number);
        }
        return true;
    }

}

bool Sqlite::updateOne(int id,int number)     //修改某一个对象的信息
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1");
    QSqlQuery query(db);
    QString updateSql = "update goods set number = ? where id = ?";
    query.prepare(updateSql);
    query.addBindValue(number);
    query.addBindValue(id);
    if(!query.exec())
    {
        qDebug()<<"修改失败";
        return false;
    }
    else
    {
        return true;
    }
}
